﻿using BCCommon;
using BCCommon.TMS.Insurance;
using BCEntity.TMS.PersonalInsuranceRecord;
using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;

namespace BCData.TMS.PersonalInsuranceRecord
{
    public class PersonalInsuranceRecordData : IPersonalInsuranceRecordData
    {
        private readonly IDatabaseContext databaseContext;
        private const string QUERY = @"SELECT PersonalInsuranceRecordId,ElectricInsurancePath,UnderwritingCompany,Policyholder,Insured,Content,IdentityCardNo,InsuredAmount,Premium,EffectiveTime,ExpirationTime,CreateTime,Status,CompanyId,DriverId FROM PersonalInsuranceRecord";


        public PersonalInsuranceRecordData(IDatabaseContext databaseContext)
        {
            this.databaseContext = databaseContext;
        }


        public async Task<bool> DeleteAsync(long personalInsuranceRecordId)
        {
            string sql = @"DELETE FROM PersonalInsuranceRecord WHERE PersonalInsuranceRecordId=?PersonalInsuranceRecordId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?PersonalInsuranceRecordId", personalInsuranceRecordId, DbType.Int64);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }

        public async Task<(IEnumerable<PersonalInsuranceRecordEntity>, int)> GetAllAsync(long? companyId, string underwritingCompany, string insured, string identityCardNo, InsuranceStatus? status, int pageIndex, int pageSize, bool? ascending, string sortName)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (companyId.HasValue)
            {
                wheres.Add("CompanyId = ?CompanyId");
                parameters.Add("?CompanyId", companyId.Value, DbType.Int64);
            }
            if (status.HasValue)
            {
                wheres.Add("Status = ?Status");
                parameters.Add("?Status", status.Value.ToString(), DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(underwritingCompany))
            {
                wheres.Add("UnderwritingCompany = ?UnderwritingCompany");
                parameters.Add("?UnderwritingCompany", underwritingCompany, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(insured))
            {
                wheres.Add("Insured LIKE CONCAT('%',CONCAT(?Insured,'%'))");
                parameters.Add("?Insured", insured, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(identityCardNo))
            {
                wheres.Add("IdentityCardNo = ?IdentityCardNo");
                parameters.Add("?IdentityCardNo", identityCardNo, DbType.AnsiString);
            }
            string sql = string.Format(@"{0} {1} ORDER BY {4} {5} LIMIT {2},{3}; SELECT COUNT(1) FROM PersonalInsuranceRecord  {1}",
                QUERY,
                wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
                (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize,
                pageSize,
                string.IsNullOrWhiteSpace(sortName) ? "PersonalInsuranceRecordId" : sortName,
                ascending.HasValue && ascending.Value ? "ASC" : "DESC");
            return await this.databaseContext.Slave.QueryMultipleAsync<PersonalInsuranceRecordEntity, int>(sql, parameters);
        }

        public async Task<PersonalInsuranceRecordEntity> InserAsync(PersonalInsuranceRecordEntity entity)
        {
            string sql = SqlCommands.GetInsertSql("PersonalInsuranceRecord", new[] { "ElectricInsurancePath", "UnderwritingCompany", "Policyholder", "Insured", "Content", "IdentityCardNo", "InsuredAmount", "Premium", "EffectiveTime", "ExpirationTime", "Status", "CompanyId", "DriverId" });
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?CompanyId", entity.CompanyId, DbType.Int64); 
            parameters.Add("?ElectricInsurancePath", entity.ElectricInsurancePath, DbType.AnsiString, size: 1000);
            parameters.Add("?UnderwritingCompany", entity.UnderwritingCompany, DbType.AnsiString, size: 100);
            parameters.Add("?Policyholder", entity.Policyholder, DbType.AnsiString, size: 100);
            parameters.Add("?Insured", entity.Insured, DbType.AnsiString, size: 100);
            parameters.Add("?Content", entity.Content, DbType.AnsiString, size: 2000);
            parameters.Add("?IdentityCardNo", entity.IdentityCardNo, DbType.AnsiString, size: 18);
            parameters.Add("?InsuredAmount", entity.InsuredAmount, DbType.Decimal);
            parameters.Add("?Premium", entity.Premium, DbType.Decimal);
            parameters.Add("?EffectiveTime", entity.EffectiveTime, DbType.DateTime);
            parameters.Add("?ExpirationTime", entity.ExpirationTime, DbType.DateTime);
            parameters.Add("?Status", entity.Status, DbType.AnsiString);
            parameters.Add("?DriverId", entity.DriverId, DbType.Int64);
            sql = string.Format("{0} {1} WHERE PersonalInsuranceRecordId = LAST_INSERT_ID()", sql, QUERY);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<PersonalInsuranceRecordEntity>(sql, parameters);
        }

        public async Task<PersonalInsuranceRecordEntity> UpdateAsync(PersonalInsuranceRecordEntity entity)
        {
            string sql = SqlCommands.GetUpdateSql("PersonalInsuranceRecord", new[] { "ElectricInsurancePath", "UnderwritingCompany", "Policyholder", "Insured", "Content", "IdentityCardNo", "InsuredAmount", "Premium", "EffectiveTime", "ExpirationTime", "Status", "CompanyId", "DriverId" }, new[] { "PersonalInsuranceRecordId" });
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?PersonalInsuranceRecordId", entity.PersonalInsuranceRecordId, DbType.Int64);
            parameters.Add("?CompanyId", entity.CompanyId, DbType.Int64); ;
            parameters.Add("?ElectricInsurancePath", entity.ElectricInsurancePath, DbType.AnsiString, size: 1000);
            parameters.Add("?UnderwritingCompany", entity.UnderwritingCompany, DbType.AnsiString, size: 100);
            parameters.Add("?Policyholder", entity.Policyholder, DbType.AnsiString, size: 100);
            parameters.Add("?Insured", entity.Insured, DbType.AnsiString, size: 100);
            parameters.Add("?Content", entity.Content, DbType.AnsiString, size: 2000);
            parameters.Add("?IdentityCardNo", entity.IdentityCardNo, DbType.AnsiString, size: 18);
            parameters.Add("?InsuredAmount", entity.InsuredAmount, DbType.Decimal);
            parameters.Add("?Premium", entity.Premium, DbType.Decimal);
            parameters.Add("?EffectiveTime", entity.EffectiveTime, DbType.DateTime);
            parameters.Add("?ExpirationTime", entity.ExpirationTime, DbType.DateTime);
            parameters.Add("?Status", entity.Status, DbType.AnsiString);
            parameters.Add("?DriverId", entity.DriverId, DbType.Int64);
            sql = string.Format("{0} {1} WHERE PersonalInsuranceRecordId = ?PersonalInsuranceRecordId", sql, QUERY);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<PersonalInsuranceRecordEntity>(sql, parameters);
        }

        public async Task<IEnumerable<PersonalInsuranceRecordEntity>> GetListByIdentityCardNoAsync(string identityCardNo)
        {
            string sql = string.Format("{0} WHERE IdentityCardNo=?IdentityCardNo; ", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?IdentityCardNo", identityCardNo, DbType.AnsiString);
            return await this.databaseContext.Slave.QueryAsync<PersonalInsuranceRecordEntity>(sql, parameters);
        }

        public async Task<PersonalInsuranceRecordEntity> GetByPersonalInsuranceRecordIdAsync(long personalInsuranceRecordId)
        {
            string sql = string.Format("{0} WHERE PersonalInsuranceRecordId=?PersonalInsuranceRecordId; ", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?PersonalInsuranceRecordId", personalInsuranceRecordId, DbType.Int64);
            return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<PersonalInsuranceRecordEntity>(sql, parameters);
        }

        public async Task<bool> ExistsByPersonalInsuranceRecordIdAsync(long personalInsuranceRecordId)
        {
            string sql = @"SELECT 1 FROM PersonalInsuranceRecord WHERE PersonalInsuranceRecordId=?PersonalInsuranceRecordId; ";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?PersonalInsuranceRecordId", personalInsuranceRecordId, DbType.Int64);
            return await this.databaseContext.Slave.ExecuteScalarAsync<int>(sql, parameters) > 0;
        }
    }
}
